package com.practicesuite.systemlayer;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.practicesuite.logging.MyLogger;
import com.practicesuite.logging.UseLogger;
import com.practicesuite.storage.*;

/**
 * System Driver Class
 * This class is the brains behind the overall system.  It will be housed in separate jar instsance and be completly 
 * seperate from the control layer part of the system
 * @author Ronald Brown
 *
 */


public class SystemDriver {
    private final static Logger LOGGER = Logger.getLogger(UseLogger.class
            .getName());
    
    /*
     * TODO: 
     * 
     * editUser needs to be fixed
     * 
     * 
     * 
     * Done
         Make it so treatment records can be queried from doctor's orders

        getTreatmentRecord(patient p)
        getAllTreatmentRecords()

        Make a treatment record also hold a doctorsorder

        Invoice getter/setter retrieval from database
        
        doctor order number associated with each tretment record
        getTreatmentRecordsFromPatientID(int patientId)

      
     */
	
    //Singleton server variable
    private static SystemDriver instance;
    private Vector v;
    private boolean inUse;
    private static boolean logOn = false;

    private SystemDriver()
    {
        System.out.println("private method called");
      v = new Vector();
      v.addElement(new Object());
      inUse = true;
      setupLog();
      
      
      
    }
    
    

    public static SystemDriver getInstance()
    {
      if (instance == null)
      {
        synchronized(SystemDriver.class) {
          if (instance == null)          
            instance = new SystemDriver();  
        }
      }
      setupLog();
     // LOGGER.warning("Info Log");
     // LOGGER.info("Info Log");
     // LOGGER.finest("Really not important");
      
      return instance;
    }


    //TESTING VARIABLES ONLY
    private static final String uNamePatient = "Ron";
    private static final char[] pWordPatient = "gatech".toCharArray();
    private static final String uNameNurse = "Ronda";
    private static final char[] pWordNurse = "gatech".toCharArray();
    /**
     * requestLogin Method
     * @param userName Username to be verified
     * @param password Password to be verified
     * @param trys number of attempted unsuccessful logins
     * @return
     */
    public static int requestLogin(String userName, char[] passwords, int tries) throws Exception {
        setupLog();
        int id = -1;
        int userid = -1;
        if(tries >= 4) {
            throw new Exception("Your account has been locked, maximum trys reached");
        }
        else {
            //Database call here
            MyQuery mq = new MyQuery();
            //String[] param = {name};
           // System.out.println("user " + userName);
            String password = String.valueOf(passwords);
            //System.out.println("password " + password);
            String[] params = {userName, password.toString()};
            ResultSet rs, rs2;
            try {
                rs = mq.executeQueryWhere("SELECT * FROM ron.userlogin WHERE username = ? " + 
                       "and password = ?" , params );
                
            }
            catch(Exception ex) {
                LOGGER.severe("Failed Username: " + userName + " Failed PW: " + String.valueOf(passwords) + ex.toString() );
                throw new Exception("Unable to connect please try later");
                
            }

            //Aassert(rs != null);
            while(rs.next()) {
                id = rs.getInt("euid");
                userid = rs.getInt("loginid");
                
            }
            String[] where = {Integer.toString(userid)};
            rs2 = mq.executeQueryWhere("select suspended from ron.userinfo where loginid = ?", where);
            
            while(rs2.next()) {
                if (rs2.getInt("suspended") == 1) {
                    throw new Exception("Your account is suspended");
                }
            }
            
            if (id == -1) {
                throw new Exception("Invalid login information");
            }
            
            
            else {
                LOGGER.warning("User " + userName + " has logged in at " + new Date().getTime());
                return id;
            }
            

        }
    }
    
    /**
     * 
     * @param userId
     * @return
     * @throws Exception
     */
    public static String retrievePassword(int userId) throws Exception {
        setupLog();
        MyQuery mq = new MyQuery();
        String userPassword = "";
        String[] params = {Integer.toString(userId)};
        ResultSet rs = null;
        
        try {
           rs =  mq.executeQueryWhere("Select password from ron.userlogin where loginid = ?", params);
        }
        catch(Exception ex) {
            LOGGER.severe("Failed to retrieve password for userid: " + userId);
            throw new Exception(ex.toString());
        }
        

        
        while(rs.next()) {
            userPassword = rs.getString("password");
        }
        return userPassword;
    }
    

    /**
     * 
     * @param userName
     * @param password
     * @return
     * @throws Exception
     */
    public static Admin getAdmin(String userName, char[] password) throws Exception {
     setupLog();
     Admin a = new Admin();
     MyQuery mq = new MyQuery();
     ResultSet rs = null;
     String[] params = {userName, String.valueOf(password)};
     
     try {
         rs = mq.executeQueryWhere("select ui.* from ron.userinfo ui inner join " +
         		"ron.userlogin ul on ui.loginid = ul.loginid " +
         		"WHERE ul.username = ? AND ul.password = ?", params);
           }
     catch (Exception ex) {
         LOGGER.warning(ex.toString());
     }
     
     while(rs.next()) {
        a = new Admin(rs.getString("name"), rs.getInt("euid")
                 , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                 , rs.getString("gender"), rs.getInt("age")
                 , rs.getString("emailaddr"), rs.getBoolean("suspended"));
       
     }

     return a;
    }
    
    /**
     * 
     * @param userName
     * @param password
     * @return
     * @throws Exception
     */
    public static Doctor getDoctor(String userName, char[] password) throws Exception {
        setupLog();
        Doctor d = new Doctor();
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String[] params = {userName, String.valueOf(password)};
        
        try {
            rs = mq.executeQueryWhere("select ui.* from ron.userinfo ui inner join " +
                   "ron.userlogin ul on ui.loginid = ul.loginid " +
                   "WHERE ul.username = ? AND ul.password = ?", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        
        while(rs.next()) {
           d = new Doctor(rs.getString("name"), rs.getInt("euid")
                    , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , rs.getString("emailaddr"), rs.getBoolean("suspended"));
        }
        
        return d;
    }
    
    /**
     * 
     * @param userName
     * @param password
     * @return the nurse corresponding to userName and password
     * @throws Exception 
     */
    public static Nurse getNurse(String userName, char[] password) throws Exception {
        setupLog();
        Nurse n = new Nurse();
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String[] params = {userName, String.valueOf(password)};
        
        try {
            rs = mq.executeQueryWhere("select ui.* from ron.userinfo ui inner join " +
                   "ron.userlogin ul on ui.loginid = ul.loginid " +
                   "WHERE ul.username = ? AND ul.password = ?", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        
        while(rs.next()) {
           n = new Nurse(rs.getString("name"), rs.getInt("euid")
                    , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , rs.getString("emailaddr"),rs.getBoolean("suspended"));
        }
        
        return n;
    }
    
    /**
     * 
     * @param userName
     * @return the patient corresponding to userName
     * @throws Exception
     */
    public static Patient getPatient(String userName) throws Exception {
     //Lookup by username
        setupLog();
        MyQuery mq = new MyQuery();
       int id = 0;
        String[] params = {userName};
        ResultSet rs;
        Patient p = new Patient();
        try {
            rs = mq.executeQueryWhere("SELECT ui.*, mi.* from ron.userinfo ui inner join " +
            		"ron.userlogin ul on ui.loginid = ul.loginid " +
            		"INNER JOIN ron.medinfo mi " +
            		"on mi.userid = ui.loginid WHERE ul.username = ?", 
                    params );
            
            
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            throw new Exception("Unable to connect please try later");
        }
        

        
        while(rs.next()) {
            id = rs.getInt("euid");
            p = new Patient(rs.getString("name"), rs.getInt("euid")
                    , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age"), new MedicalInformation(rs.getInt("loginid"),
                            rs.getString("pharmarcy"), rs.getString("allergies"), rs.getString("insurance"))
                    , rs.getString("emailaddr"), rs.getBoolean("suspended"));
            
            
        }
        
        //Now that we have id let's build object
        return p;
    }
 
    /**
     * This method creates a new, base user
     * @param name
     * @param eUID
     * @param userID
     * @param dateOfBirth
     * @param address
     * @param phoneNumber
     * @param gender
     * @param age
     * @return
     * @throws Exception 
     */
    public static Patient createPatient(String name, Date dateOfBirth
            , String address, String phoneNumber
            , String gender, int age, String emailAddress
            , String userName, char[] password, int userType, MedicalInformation medInfo) throws Exception {
        

        setupLog();
        MyQuery mq = new MyQuery();
       MyInsert mi = new MyInsert();
        String passPassword = String.valueOf(password);
       
        String[] params = {userName,passPassword,Integer.toString(userType)};
        ResultSet rs;
        //Add user to login table
       
        //add patient to user table
        try {
            mq.executeUpdateWhere("INSERT INTO ron.userlogin (username, password, euid) VALUES(?,?,?)", 
                    params );
            //mi.executeInsertStatement("ron.userlogin", params);
            
            
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            throw new Exception("Unable to connect please try later");
        }
        

        
      //Get their info
        int savedUID = 0;
        String[] params2 = {userName};
        try {
            rs = mq.executeQueryWhere("SELECT loginid from ron.userlogin where username = ?" , params2 );
            
        }
        catch(Exception ex) {
            throw new Exception("Unable to connect please try later");
        }
        
        while(rs.next()) {
            savedUID = rs.getInt("userlogin.loginid");
        }
        
        
        //Insert into users table
        //int pNum = Integer.parseInt(phoneNumber);
        String[] nParams = {"1234-12-12" ,Integer.toString(userType), name, emailAddress, address, phoneNumber,gender, Integer.toString(age), Integer.toString(savedUID)};
        try {
            mq.executeUpdateWhere("INSERT INTO ron.userinfo " + 
           " (dob, euid, name,emailaddr,address,phone,gender,age,loginid) " + 
           "  VALUES(?,?,?,?,?,?,?,?,?)", nParams );
            
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            throw new Exception("Unable to connect please try later");
        }
  
        Patient p =  new Patient(name, 3, savedUID, dateOfBirth, address, phoneNumber
                , gender, age, null, emailAddress, false);
        
        try {
            String[] paramz = {Integer.toString(p.getUserID()),"2011-05-11", "0"};
           // mi.executeInsertStatement("ron.patient", paramz);
            mq.executeUpdateWhere("INSERT INTO ron.patient (pid, date_reg, registereddoc) VALUES(?,?,?)", paramz);
            paramz = null;
            paramz = new String[]{medInfo.getPharmCarrier(), medInfo.getInsurance()
                    , medInfo.getAllergies(),Integer.toString(p.getUserID()) };
            mq.executeUpdateWhere("insert into ron.medinfo (pharmarcy, insurance, allergies, userid) VALUES (?,?,?,?)", paramz);
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
        }
        return p;
    }
    
    /**
     * 
     * @param name
     * @param dateOfBirth
     * @param address
     * @param phoneNumber
     * @param gender
     * @param age
     * @param emailAddress
     * @param userName
     * @param password
     * @param adminObject
     * @param userType
     * @throws Exception
     */
    public static int createUser(String name, Date dateOfBirth
            , String address, String phoneNumber
            , String gender, int age, String emailAddress
            , String userName, char[] password, Object adminObject, int userType) throws Exception {
        setupLog();
        if(adminObject instanceof Admin == false) {
            return 0;
        }
        
        Admin admin = (Admin)adminObject;
        
        MyQuery mq = new MyQuery();
        MyInsert mi = new MyInsert();
        String passPassword = String.valueOf(password);
       
        String[] params = {userName,passPassword,Integer.toString(userType)};
        ResultSet rs;
        //Add user to login table
       
      
        try {
            mq.executeUpdateWhere("INSERT INTO ron.userlogin (username, password, euid) VALUES(?,?,?)", 
                    params );
            
            
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            throw new Exception("Unable to connect please try later");
        }

      //Get their info
        int savedUID = 0;
        String[] params2 = {userName};
        try {
            rs = mq.executeQueryWhere("SELECT loginid from ron.userlogin where username = ?" , params2 );
            
        }
        catch(Exception ex) {
            throw new Exception("Unable to connect please try later");
        }
        
        while(rs.next()) {
            savedUID = rs.getInt("loginid");
        }
        
        
        //Insert into users table
       // int pNum = Integer.parseInt(phoneNumber);
        String[] nParams = {name, emailAddress, address, phoneNumber,gender, Integer.toString(age), "-", "-", "-", Integer.toString(savedUID)};
        try {
            mq.executeUpdateWhere("INSERT INTO ron.userinfo " + 
           " (name,emailaddr,address,phone,gender,age,pharmarcy,insurance,allergies,loginid) " + 
           "  VALUES(?,?,?,?,?,?,?,?,?,?,?)", nParams );
            
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            throw new Exception("Unable to connect please try later");
        }

        
        return 1;
        
    }

    /**
     * This method returns the TreatmentRecord for a given patient
     * @param p The Patient who owns the TreatmentRecord
     * @return The TreatmentRecord for the Patient
     * @throws SQLExceptionm
     */
    public static List<TreatmentRecord> getTreatmentRecordList(Patient p) throws Exception {
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        TreatmentRecord tr = null;
        List<TreatmentRecord> tl = new ArrayList<TreatmentRecord>();
        String[] params = {Integer.toString(p.getUserID())};
        
        try {
            rs = mq.executeQueryWhere("select do.recordid, do.labwork, " +
            		"do.followupinstr, do.otherinstr, tr.* " +
            		"from ron.docorder do inner " +
            		"join ron.treatment tr on " +
            		"do.trecid = tr.id where tr.pid = ? order by enddate", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }

        while(rs.next()) {
          //  DoctorOrder docO = getDoctorOrderFromID(rs.getInt("docorderid"));
            String vits = rs.getString("vitals");
            String[] vitalsArray = vits.split(",");
            
            int[] vitals = {Integer.parseInt(vitalsArray[0]), Integer.parseInt(vitalsArray[1]), Integer.parseInt(vitalsArray[2]), Integer.parseInt(vitalsArray[3]), Integer.parseInt(vitalsArray[4])};
           
            tr = new TreatmentRecord(rs.getInt("docid"), rs.getDate("enddate"), rs.getInt("nurseid"),
                    rs.getString("complaint"), vitals, rs.getString("patientcond"), rs.getInt("id"));
            tr.setTime(rs.getTime("enddate"));
            tl.add(tr);
        }
        
        
        
        return tl; 
    }
    
    /**
     * This method returns all appointnments for the practice
     * @return
     * @throws SQLException 
     */
    public static List<Appointment> getAppointments() throws Exception {
        List<Appointment> lp = new ArrayList<Appointment>();
        MyQuery mq = new MyQuery();
        ResultSet rs = null;

        
        try {
            rs = mq.executeQueryStatement("SELECT app.* , ui.* FROM ron.appointment " +
            		"app inner join ron.userinfo ui ON app.pid = ui.loginid");
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }

        while(rs.next()) {
            Appointment a = new Appointment();
            Patient p = new Patient(rs.getString("name"), 
                    3, rs.getInt("loginid"), rs.getDate("dob")
                    , rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , null, rs.getString("emailaddr"), rs.getBoolean("suspended"));
            a.setAppointmentTime(rs.getTime("apptime").toString());
            a.setDesiredDoctorID(rs.getInt("docid"));
            a.setReasonForApp(rs.getString("reason"));
            a.setAppDate(rs.getString("appdate"));
            a.setPatient(p);
            
            lp.add(a);
        }
        
        
        
        
        return lp;
    }
    
    /**
     * This method returns the Appointnment list for a specified userID
     * @param userID
     * @return
     */
    public static List<Appointment> getAppointments(int userID) throws Exception {
        setupLog();
        List<Appointment> lp = new ArrayList<Appointment>();
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String query = "SELECT app.* , ui.* FROM ron.appointment app inner join ron.userinfo ui ON app.pid = ui.loginid inner join ron.userlogin ul ON ui.loginid = ul.loginid WHERE ui.loginid=" + userID;
        
        try {
            rs = mq.executeQueryStatement(query);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }

        while(rs.next()) {
            Appointment a = new Appointment();
            Patient p = new Patient(rs.getString("name"), 
                    3, rs.getInt("loginid"), rs.getDate("dob")
                    , rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , null, rs.getString("emailaddr"), rs.getBoolean("suspended"));
            a.setAppointmentTime(rs.getTime("apptime").toString());
            a.setDesiredDoctorID(rs.getInt("docid"));
            a.setReasonForApp(rs.getString("reason"));
            a.setAppDate(rs.getString("appdate"));
            a.setPatient(p);
            
            lp.add(a);
        }        
        return lp;
    }
    
    public static Nurse getNurseFromID(int id) throws SQLException {
        setupLog();
        Nurse d = new Nurse();
        
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String params[] = {Integer.toString(id)};
        
        try {
            rs = mq.executeQueryWhere("SELECT ui.* from ron.userinfo " + 
                    "ui inner join ron.nurse nur ON nur.nurseid = ui.loginid where nur.nurseid = ?"
                    , params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }

        while(rs.next()) {
            d = new Nurse(rs.getString("name"), rs.getInt("euid")
                    , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , rs.getString("emailaddr"), rs.getBoolean("suspended"));
            return d;
        }
        return d;
        
    }
    

    /**
     * 
     * @param appointnmentType
     * @param appointnmentLength
     * @param createdBy
     * @param reSchedule
     * @param patient
     * @param reasonForApp
     * @param desiredDoctor
     * @param appTime
     * @param appDate
     * @return
     */
    public static Appointment createAppointment(int appointnmentType, int appointnmentLength
            , int createdBy,boolean reSchedule
            ,Patient patient, String reasonForApp
            , String desiredDoctor, String appTime,  String appDate) {
        setupLog();
        MyQuery mi = new MyQuery();
        try {
        String[] info = {Integer.toString(getDocBasedOnName(desiredDoctor))
                , Integer.toString(patient.getUserID())
                , appDate, appTime, reasonForApp};
        
        mi.executeUpdateWhere("INSERT INTO ron.appointment " +
        		"(docid, pid, appdate, apptime, reason) " +
        		"VALUES (?,?,?,?,?)", info);
        
        
        return new Appointment(appointnmentType, appointnmentLength, createdBy
            , reSchedule, patient, reasonForApp
            , getDocBasedOnName(desiredDoctor), appTime, appDate);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        return null;
    }
    
    public static List<TreatmentRecord>getTreatmentRecordsFromPatientID(int patientId) throws Exception {
        setupLog();
        List<TreatmentRecord> trl = new ArrayList<TreatmentRecord>();
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String[] where = {Integer.toString(patientId)};
        try {
            mq.executeQueryWhere("select * from ron.treatment where pid = ?", where);
        }
        catch(Exception ex) {
            
        }
        while(rs.next()) {
            int[] vitals = {0,0,0,0,0};
            //DoctorOrder docO = getDoctorOrderFromID(rs.getInt("docorderid"));
            TreatmentRecord tr = new TreatmentRecord(rs.getInt("docid"), rs.getDate("enddate"), rs.getInt("nurseid"),
                    rs.getString("complaint"), vitals, rs.getString("patientcond"), rs.getInt("id"));
            trl.add(tr);
        }
        
        return trl;
    }

    /**
     * Get Doctor List
     * @return Returns list of doctor's in the system
     * @throws Exception
     */
    public static List<Doctor> getDoctorList() throws Exception {
        setupLog();
        MyQuery mq = new MyQuery();
        List<Doctor> doctors = new ArrayList<Doctor>();
        
        try {
            ResultSet rs = mq.executeQueryStatement("SELECT ui.*, doc.* FROM ron.userinfo ui inner join ron.userlogin ul on ul.loginid = ui.loginid inner join ron.doctor doc on docid = ui.loginid");
            while(rs.next()) {
                Doctor d = new Doctor(rs.getString("name"),rs.getInt("euid")
                        ,rs.getInt("loginid"),rs.getDate("dob")
                        ,rs.getString("addr"),rs.getString("phone")
                        ,rs.getString("gender"),rs.getInt("age")
                        ,rs.getString("emailaddr"), rs.getBoolean("suspended"));
                doctors.add(d);
            }
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        
       finally {
           mq.closeConnection();
       }
       return doctors;
    }
    
    /**
     * Get Doctor from Name
     * @param name Name of the Doctor
     * @return returns the name of the doctor
     * @throws Exception
     */
    private static int getDocBasedOnName(String name) throws Exception {
        setupLog();
        MyQuery mq = new MyQuery();
        int docid = -1;
        String[] params = {name};
        ResultSet rs = null;
        try {
            rs = mq.executeQueryWhere("SELECT ui.* from ron.userinfo ui " +
            		"inner join ron.doctor doc on doc.docid = ui.loginid" +
            		" WHERE ui.name = ?", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        
        while(rs.next()) {
            int id = rs.getInt("loginid");
            return id;
        }
        
        
        return 0;
    }
    
    /**
     * 
     * @return list of all patients in the system
     * @throws Exception
     */
    public static List<Patient> getPatientList() throws Exception{
        setupLog();
        MyQuery mq = new MyQuery();
        List<Patient> patients = new ArrayList<Patient>();
        
        try {
            ResultSet rs = mq.executeQueryStatement("SELECT ui.*, p.*, mi.allergies, mi.pharmarcy, mi.insurance FROM ron.userinfo ui inner join ron.userlogin ul on ul.loginid = ui.loginid inner join ron.patient p on pid = ui.loginid inner join ron.medinfo mi on mi.userid = ui.loginid");
            while(rs.next()) {
                MedicalInformation m = new MedicalInformation(rs.getInt("loginid"),rs.getString("pharmarcy"),rs.getString("allergies"),rs.getString("insurance"));
                Patient p = new Patient(rs.getString("name"),rs.getInt("euid"),rs.getInt("loginid")
                        ,rs.getDate("dob"),rs.getString("address"),rs.getString("phone")
                        ,rs.getString("gender"),rs.getInt("age"),m
                        ,rs.getString("emailaddr"), rs.getBoolean("suspended"));
                patients.add(p);
            }
            return patients;
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        
       finally {
           mq.closeConnection();
       }
    }
    
    /**
     * 
     * @param name
     * @return
     * @throws Exception
     */
    public static List<Patient> getPatientList(String name) throws Exception{
        setupLog();
        MyQuery mq = new MyQuery();
        List<Patient> patients = new ArrayList<Patient>();
        String query = "SELECT ui.*, p.*, mi.allergies, mi.pharmarcy, mi.insurance FROM ron.userinfo ui inner join ron.userlogin ul on ul.loginid = ui.loginid inner join ron.patient p on pid = ui.loginid inner join ron.medinfo mi on mi.userid = ui.loginid where ui.name=" + name;
        
        try {
           // ResultSet rs = mq.executeQueryStatement(query);
            String[] params  = {name};
            ResultSet rs = mq.executeQueryWhere("SELECT ui.*, p.*, mi.allergies, mi.pharmarcy, mi.insurance FROM ron.userinfo ui inner join ron.userlogin ul on ul.loginid = ui.loginid inner join ron.patient p on pid = ui.loginid inner join ron.medinfo mi on mi.userid = ui.loginid where ui.name= ?"
        , params);
            while(rs.next()) {
                MedicalInformation m = new MedicalInformation(rs.getInt("loginid"),rs.getString("pharmarcy"),rs.getString("allergies"),rs.getString("insurance"));
                Patient p = new Patient(rs.getString("name"),rs.getInt("euid")
                        ,rs.getInt("loginid"),rs.getDate("dob"),rs.getString("address")
                        ,rs.getString("phone"),rs.getString("gender")
                        ,rs.getInt("age"),m,rs.getString("emailaddr"), rs.getBoolean("suspended"));
                patients.add(p);
            }
            return patients;
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        
       finally {
           mq.closeConnection();
       }
    }
    
    /**
     * Delete Treatment record
     * @param dTrecId treatment record id to delete
     */
    public static void deleteTreatmentRecord(int dTrecId) {
        setupLog();
        
        MyQuery mq = new MyQuery();
        
        String[] where = {Integer.toString(dTrecId)};
        try {
        mq.executeUpdateWhere("DELETE FROM ron.treatment where id = ?", where);
        mq.executeUpdateWhere("DELETE FROM ron.docorder where trecid = ?", where);
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * Delete Doctor Order
     * @param dOrderId doctor order id to delete
     */
    public static void deleteDocOrder(int dOrderId) {
        setupLog();
        MyQuery mq = new MyQuery();
        
        String[] where = {Integer.toString(dOrderId)};
        
        mq.executeUpdateWhere("DELETE FROM ron.docorder where recordid = ?", where);
    }
    
    /**
     * Update patient method
     * @param p Patient to be updated
     * @return status 0 if fail or 1 if success
     * @throws Exception
     */
    public static int updatePatient(Patient p) throws Exception{
        setupLog();
        int success = 0;
        MyQuery mq = new MyQuery();
        String[] param1 = {p.getName(), p.getAddress(), p.getPhoneNumber(), p.getGender(), Integer.toString(p.getAge()), p.getEmail(), Integer.toString(p.getUserID())};
        String[] param2 = {p.getMedicalInformation().getPharmCarrier(), p.getMedicalInformation().getInsurance(), p.getMedicalInformation().getAllergies(), Integer.toString(p.getUserID())};
        
        try {
            mq.executeUpdateWhere("UPDATE ron.userinfo SET name = ?, address = ?, phone = ?, gender = ?, age = ?, emailaddr = ? WHERE userid = ?", param1);
            mq.executeUpdateWhere("UPDATE ron.medinfo SET pharmarcy = ?, insurance = ?, allergies = ? WHERE userid = ?", param2);
            success = 1;
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        

       return success;    
    }
   
    /**
     * Get Doctor by ID Method
     * @param id
     * @return
     * @throws Exception
     */
    public static Doctor getDoctorById(int id) throws Exception {
        setupLog();
        Doctor d = new Doctor();
        
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        String params[] = {Integer.toString(id)};
        
        try {
            rs = mq.executeQueryWhere("SELECT ui.*, doc.* from ron.userinfo " +
            		"ui inner join ron.doctor doc ON doc.docid = ui.loginid where doc.docid = ?"
                    , params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }

        while(rs.next()) {
            d = new Doctor(rs.getString("name"), rs.getInt("euid")
                    , rs.getInt("loginid"), rs.getDate("dob"),rs.getString("address"), rs.getString("phone")
                    , rs.getString("gender"), rs.getInt("age")
                    , rs.getString("emailaddr"), rs.getBoolean("suspended"));
            return d;
        }
        
        return d;
    } 
    
    
    /**
     * 
     * @param p
     * @return
     * @throws Exception
     */
    public static int deletePatient(Patient p) throws Exception{
        setupLog();
        int success = 0;
        MyQuery mq = new MyQuery();
        String[] param = {Integer.toString(p.getUserID())};
        
        try {
            mq.executeQueryWhere("DELETE from ron.userinfo where userid =" , param);
            success = 1;
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        

       return success;
    }
    

    /**
     * 
     * @param t the treatment record to be stored
     * @return
     * @throws Exception
     */
    public static int createTreatmentRecord(TreatmentRecord t, int patientID) {
        setupLog();
        MyQuery mi = new MyQuery();
        ResultSet rs = null;
        
        String s = "";
        for(int i = 0; i < t.getVitalSigns().length; i++) {
            s += t.getVitalSigns()[i] + ",";
        }
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String[] params = {Integer.toString(t.getTreatingDoctor()), Integer.toString(patientID)
                , t.getChiefComplaint(), t.getDiagnosis()
                , df.format(t.getDateTime()), df.format(t.getDateTime()), Integer.toString(t.getAttendingNurse())
                , t.getChiefComplaint(), s};
        try {
            mi.executeUpdateWhere("INSERT INTO ron.treatment (docid, pid, patientcond" +
            		",patienttreatment,startdate,enddate,nurseid" +
            		",complaint,vitals) VALUES(?,?,?,?,?,?,?,?,?)", params);
            
          String[] where = {df.format(t.getDateTime()), df.format(t.getDateTime()), t.getChiefComplaint()};
          rs =  mi.executeQueryWhere("Select id from ron.treatment where " +
          		"startdate = ? AND enddate = ? AND complaint = ?", where);
          while(rs.next()) {
              int id = rs.getInt("id");
              return id;
          }
            
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        //if(mi.executeInsertStatement("ron.treatment", params))
        return -1;
    }

    

    /**
     * 
     * @param d the doctor order to be stored
     * @return
     * @throws Exception
     */
    public static int createDoctorsOrders(DoctorOrder d, int treatmentRecordId) {
        setupLog();
        MyQuery mq = new MyQuery();
        StringBuilder sb = new StringBuilder();
        
        for(String s : d.getLabWorkPreformed()) {
            sb.append(s + " ");
        }
        String[] params = {sb.toString(), d.getFollowUpInstructions(), d.getOtherInstructions()
                ,Integer.toString(d.getTreatingDoctor()), Integer.toString(treatmentRecordId)};

        try {
            mq.executeUpdateWhere("INSERT INTO ron.docorder (labwork, followupinstr" +
            		", otherinstr, docid, trecid) " +
            		"VALUES(?,?,?,?,?)", params);
            String[] where = {Integer.toString(treatmentRecordId)};
            return 1;
        }
        catch(Exception ex) {
            return 0;
        }

        

    }
    
   
    
    /**
     * 
     * @return the list of all nurses in the system
     * @throws Exception
     */
    public static List<Nurse> getNurseList() throws Exception{
        setupLog();
        MyQuery mq = new MyQuery();
        List<Nurse> nurses = new ArrayList<Nurse>();
        
        try {
            ResultSet rs = mq.executeQueryStatement("SELECT ui.*, n.* FROM ron.userinfo ui inner join ron.userlogin ul on ul.loginid = ui.loginid inner join ron.nurse n on nid = ui.loginid");
            while(rs.next()) {
                Nurse n = new Nurse(rs.getString("name"),rs.getInt("euid")
                        ,rs.getInt("loginid"),rs.getDate("dob"),rs.getString("address")
                        ,rs.getString("phone"),rs.getString("gender"),rs.getInt("age")
                        ,rs.getString("emailaddr"), rs.getBoolean("suspended"));
                nurses.add(n);
            }
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        
       finally {
           mq.closeConnection();
       }
       return nurses;
    }
    
    
    /**
     * 
     * @param name the name of the patient to be returned
     * @return the patient if found, null otherwise
     * @throws Exception
     */
    public static Patient lookupPatientByName(String userName) throws Exception {
        setupLog();
        if (userName == "") {
        
            return null;
        }
        String[] params = {userName};
        MyQuery mq = new MyQuery();
                
        try {
            ResultSet rs = mq.executeQueryWhere("SELECT ui.*, p.*, mi.allergies, mi.pharmarcy, " +
            		"mi.insurance FROM ron.userinfo ui inner join ron.userlogin " +
            		"ul on ul.loginid = ui.loginid inner join ron.patient p " +
            		"on pid = ui.loginid inner join ron.medinfo mi on " +
            		"mi.userid = ui.loginid where ul.username=?"
                , params);
                //mq.executeQueryStatement(query);
            if(rs.next()) {
                MedicalInformation m = new MedicalInformation(rs.getInt("loginid"),rs.getString("pharmarcy"),rs.getString("allergies"),rs.getString("insurance"));
                Patient p = new Patient(rs.getString("name")
                        ,rs.getInt("euid"),rs.getInt("loginid")
                        ,rs.getDate("dob"),rs.getString("address")
                        ,rs.getString("phone"),rs.getString("gender")
                        ,rs.getInt("age"),m,rs.getString("emailaddr"), rs.getBoolean("suspended"));
                return p;
            }
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }

       return null;
    }
    
    /**
     * 
     * @param loginID
     * @param adminObject
     * @return
     * @throws Exception
     */
    public static int deleteUser(int loginID) throws Exception {
        setupLog();
        int success = 0;
        MyQuery mq = new MyQuery();
        String[] param = {Integer.toString(loginID)};
        
        try {
            mq.executeUpdateWhere("DELETE from ron.userinfo where loginid =?" , param);
            mq.executeUpdateWhere("DELETE from ron.userlogin WHERE loginid=?", param);
            success = 1;
        }
        catch(Exception e) {
            LOGGER.warning(e.toString());
            throw new Exception("Unable to connect please try later");
        }
        

       return success;
        
        
    }

    /**
     * Edit User
     * @param o
     * @param userID
     * @param euid
     * @param password
     * @param suspended
     * @return
     */
    public static int editUser(User o, int userID, int euid, char[] password, boolean suspended) {

        setupLog();
        MyQuery mq = new MyQuery();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        
    try {
        //Update password
        String[] params = {String.valueOf(password), Integer.toString(euid), Integer.toString(o.getUserID())};  
        mq.executeUpdateWhere("update ron.userlogin set password = ?, euid = ? where loginid = ?", params);
        //Update user information
        int sspd = 0;
        if(o.isSuspended()) {
            sspd = 1;
        }

        String[]where = {o.getName(), df.format(o.getDateOfBirth())
                , o.getAddress(), o.getPhoneNumber(), o.getGender(), Integer.toString(o.getAge())
                , o.getEmail(), Integer.toString(sspd), Integer.toString(euid) ,Integer.toString(o.getUserID())  };
        mq.executeUpdateWhere("update ron.userinfo set name = ?" +
                ", dob = ?, address = ?, phone = ?" +
                ", gender = ?, age = ? " +
                ", emailaddr = ?, suspended = ?, euid = ? where loginid = ? ", where);
        return 1;
    }
    catch(Exception ex) {
        LOGGER.warning(ex.toString());
        ex.printStackTrace();
        return 0;
    }

    }
   

    /**
     * This method actually Saves the modified user to the database
     * @param o The user Object to be modified
     * @param userID the User's ID
     * @param euid The user's effective userid
     */
    public static int editUser(User o, int userID, int euid, boolean suspended) {
        setupLog();
        MyQuery mq = new MyQuery();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        
    try {
        //Update password
        String[] params = {Integer.toString(euid), Integer.toString(o.getUserID())};  
        mq.executeUpdateWhere("update ron.userlogin set euid = ? where loginid = ?", params);
        //Update user information
        int sspd = 0;
        if(o.isSuspended()) {
            sspd = 1;
        }

        String[]where = {o.getName(), df.format(o.getDateOfBirth())
                , o.getAddress(), o.getPhoneNumber(), o.getGender(), Integer.toString(o.getAge())
                , o.getEmail(), Integer.toString(sspd),Integer.toString(euid), Integer.toString(o.getUserID())  };
        mq.executeUpdateWhere("update ron.userinfo set name = ?" +
                ", dob = ?, address = ?, phone = ?" +
                ", gender = ?, age = ? " +
                ", emailaddr = ?, suspended = ?, euid = ? where loginid = ? ", where);
        return 1;
    }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            ex.printStackTrace();
            return 0;
        }
    }
    
    
    
    /**
     * Get All Treatment Records
     * @return Returns a list of treatment records
     * @throws Exception
     */
   public static List<TreatmentRecord> getAllTreatmentRecords() throws Exception {
       setupLog();
       MyQuery mq = new MyQuery();
       List<TreatmentRecord> tRecords = new ArrayList<TreatmentRecord>();
       
       try {
           ResultSet rs = mq.executeQueryStatement("select * from ron.treatment");
           while(rs.next()) {
               //int int string string date date
               //int[] arr = {20,40,60};
               String vits = rs.getString("vitals");
               String[] vitalsArray = vits.split(",");
               
               int[] vitals = {Integer.parseInt(vitalsArray[0]), Integer.parseInt(vitalsArray[1]), Integer.parseInt(vitalsArray[2]), Integer.parseInt(vitalsArray[3]), Integer.parseInt(vitalsArray[4])};
              
               TreatmentRecord tr;
               tr = new TreatmentRecord(rs.getInt("docid"), rs.getDate("enddate"), rs.getInt("nurseid"),
                       rs.getString("complaint"), vitals, rs.getString("patientcond"), rs.getInt("id"));
               tr.setTime(rs.getTime("enddate"));
               tRecords.add(tr);
               
           }
       }
       catch(Exception e) {
           LOGGER.warning(e.toString());
           throw new Exception("Unable to connect please try later");
       }
       
      finally {
          mq.closeConnection();
      }
      return tRecords;
   }
   
   /**
    * Get Docor Order From ID method
    * @param id The UserID to get doctor orders from
    * @return
    * @throws Exception
    */
   public static DoctorOrder getDoctorOrderFromID(int id) throws Exception {
       setupLog();
       DoctorOrder d = new DoctorOrder();
       
       MyQuery mq = new MyQuery();
       ResultSet rs = null;
       String params[] = {Integer.toString(id)};
       
       try {
           rs = mq.executeQueryWhere("select * from ron.docorder where recordid = ?"
                   , params);
       }
       catch (Exception ex) {
           LOGGER.warning(ex.toString());
       }

       //TODO: modify table to include patient id for prescription
       while(rs.next()) {
           ArrayList<Prescription> pList = new ArrayList<Prescription>();
           ArrayList<String> lWork = new ArrayList<String>();
           lWork.add(rs.getString("labwork"));
           pList.add(new Prescription("Vicoden", "Sleep"));
           d = new DoctorOrder(pList, lWork, rs.getString("followupinstr"), rs.getString("otherinstr"), rs.getInt("docid") , rs.getInt("recordid"));
           return d;
       }
       
       return d;
   }
   
   /**
    * Get Prescription List
    * @param patientId
    * @return Returns a List of prescriptions based off a patient's userID
    * @throws Exception
    */
   public static List<Prescription> getPrescriptionList(int patientId) throws Exception {
       setupLog();
       List<Prescription> list = new ArrayList<Prescription>();
       MyQuery mq = new MyQuery();
       ResultSet rs = null;
       String[] where = {Integer.toString(patientId)};
       try {
           mq.executeQueryWhere("SELECT * FROM ron.prescription WHERE patientid = ?", where);
       }
       catch(Exception ex) {
           LOGGER.warning(ex.toString());
       }
       while(rs.next()) {
           Prescription p = new Prescription(rs.getString("drug"), rs.getString("instruction"));
           list.add(p);
       }
       //LOGGER.warning("Logging call to getPrescriptionList");
       return list;
       
   }
    /**
     * Suspend Account
     * @param u Object must extend type user 
     * @param euid the object's efective userid
     * @param userId the userId to suspend
     */
    public static void suspendAccount(Object u, int euid, int userId) {
        Admin admin;
        Patient patient;
        Doctor doctor;
        Nurse nurse;
        if(euid == 0) {
            admin = (Admin)u;
            admin.setSuspended(true);
        }
        else if(euid == 3) {
            patient = (Patient)u;
            patient.setSuspended(true);
        }
        else if(euid == 1) {
            doctor = (Doctor)u;
            doctor.setSuspended(true);
        }
        else if (euid == 2) {
            nurse = (Nurse)u;
            nurse.setSuspended(true);
        }
        
        User uO = (User)u;
        
        MyQuery mq = new MyQuery();
        String[] params = {Integer.toString(userId)};
        try {
            mq.executeUpdateWhere("update ron.userinfo set suspended = 1 where loginid = ?", params);
        }
        catch(Exception ex) {
            
        }

        
        
    }
    
    
    /**
     * Get Patient's seen
     * @param month Month to query
     * @param year Year to Query
     * @return Returns int of stats
     * @throws Exception
     */
    public static int getTotalPatientsSeen(int month, int year) throws Exception {
        setupLog();
        MyQuery mq = new MyQuery();
        int totalAmount = 0;
        ResultSet rs = null;
        String[] params = {String.valueOf(month), String.valueOf(year)};
        try {
            rs = mq.executeQueryWhere("SELECT * FROM ron.stats where month = ? AND year = ?", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        
        while(rs.next()) {
            totalAmount = rs.getInt("totalpatients");
        }
        return totalAmount;
    }
    
    
    /**
     * Returns total Billing
     * @param month Month to bill
     * @param year Year to get billing from
     * @return
     * @throws Exception
     */
    public static double getTotalBilling(int month, int year) throws Exception {
        setupLog();
        MyQuery mq = new MyQuery();
        double totalAmount = 0.0;
        ResultSet rs = null;
        String[] params = {String.valueOf(month), String.valueOf(year)};
        try {
            rs = mq.executeQueryWhere("SELECT * FROM ron.stats where month = ? AND year = ?", params);
        }
        catch (Exception ex) {
            LOGGER.warning(ex.toString());
        }
        
        while(rs.next()) {
            totalAmount = rs.getDouble("totalbilling"); 
                
        }
        return totalAmount;
    }
    
    
    /**
     * Update Stats
     * @param totalBill
     * @param totalPatients
     */
    public static void updateStats(double totalBill, int totalPatients) {
        setupLog();
        MyInsert mi = new MyInsert();
        String[] info = {String.valueOf(totalPatients), String.valueOf(totalBill), String.valueOf(Calendar.MONTH)
                , String.valueOf(Calendar.YEAR) };
        try {
            mi.executeInsertStatement("ron.stats", info);
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
        }
    }
    
    public static List<User> getAllUsers() throws Exception {
        List<User> userList = new ArrayList<User>();
        
        MyQuery mq = new MyQuery();
        ResultSet rs = null;
        
        try {
            rs = mq.executeQueryStatement("select ul.username, ul.password , " +
            		"ui.* from ron.userlogin ul inner join " +
            		"ron.userinfo ui on ul.loginid = ui.loginid");
            
        } catch(Exception e) {
            LOGGER.warning(e.toString());
        }
        
        while(rs.next()) {
            userList.add(new User(rs.getString("name"), rs.getInt("euid"), rs.getInt("loginid"), rs.getDate("dob")
                    , rs.getString("address"), rs.getString("phone"), rs.getString("gender")
                    , rs.getInt("age"), rs.getString("emailaddr"), rs.getBoolean("suspended")));
            
        }
        return userList;
    }
    
    /**
     * 
     * @param patientid
     * @return
     * @throws Exception 
     */
    public static List<Invoice> getInvoice(int trecid) throws Exception {
       MyQuery mq = new MyQuery();
       //Invoice i = null;
       List<Invoice> iList = new ArrayList<Invoice>();
       ResultSet rs = null;
       
       String[] where = {Integer.toString(trecid)};
       try {
           rs = mq.executeQueryWhere("select i.*, ul.username from ron.invoices " +
           		"i inner join ron.userlogin ul on " +
           		"i.patientid = ul.loginid where trecid = ?", where);
       }catch(Exception ex){
           LOGGER.warning(ex.toString());
       }
       
       while(rs.next()) {
          Map<Double, String> services = new HashMap<Double, String>(); 
          Map<Double, String> discounts = new HashMap<Double, String>();
          
          String servicesNameWithComma = rs.getString("servicename");
          String servicesPriceWithComma = rs.getString("serviceprice");
          StringTokenizer st = new StringTokenizer(servicesNameWithComma, ",");
          StringTokenizer st2 = new StringTokenizer(servicesPriceWithComma, ",");
          
         ArrayList<String> sNames = new ArrayList<String>();
         ArrayList<Double> sNamePrices = new ArrayList<Double>();

          while(st.hasMoreTokens()) {
             sNames.add(st.nextToken());
          }
          
          while(st2.hasMoreElements()) {
             sNamePrices.add(Double.parseDouble(st2.nextToken()));
          }
          
          for(int k = 0; k < sNames.size(); k++) {
              services.put(sNamePrices.get(k), sNames.get(k));
          }
          
          String discountNameWithComma = rs.getString("discountname");
          String discountPriceWithComma = rs.getString("discountprice");
          StringTokenizer st3 = new StringTokenizer(discountNameWithComma, ",");
          StringTokenizer st4 = new StringTokenizer(discountPriceWithComma, ",");
          
         ArrayList<String> dNames = new ArrayList<String>();
         ArrayList<Double> dNamePrices = new ArrayList<Double>();

          while(st3.hasMoreTokens()) {
             dNames.add(st3.nextToken());
          }
          
          while(st4.hasMoreElements()) {
             dNamePrices.add(Double.parseDouble(st4.nextToken()));
          }
          
          for(int k = 0; k < dNames.size(); k++) {
              discounts.put(dNamePrices.get(k), dNames.get(k));
          }
        
          Invoice i = new Invoice( getPatient(rs.getString("username"))
                  , rs.getDate("date"), services
                  , discounts, rs.getInt("id"), rs.getString("docname"), rs.getInt("trecid") );
          iList.add(i);
       }
        
        return iList;
    }
    
    /**
     * Set's an invoice
     * @return
     */
    public static boolean setInvoice(Invoice i, TreatmentRecord t, String doctorName) {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        MyQuery mq = new MyQuery();
        String services = "";
        String prices = "";
        String discounts = "";
        String discountPrices = "";
        for(Map.Entry<Double,String> entry : i.getServices().entrySet()) {
            
            services += ", " +  entry.getValue();
            prices +=  ", " + entry.getKey();
        }
        
        for(Map.Entry<Double,String> entry : i.getDiscounts().entrySet()) {
            discounts += ", " + entry.getValue();
            discountPrices += ", " + entry.getKey();
        }
        String[] where = {Integer.toString(i.getPatient().getUserID())
                , df.format(i.getDate()), services
                , prices, discounts, discountPrices, doctorName, Integer.toString(t.getTreatRecId())  };
        
        try {
           mq.executeUpdateWhere("insert into ron.invoices (patientid, date" +
           		", servicename, serviceprice, discountname" +
           		", discountprice, docname, trecid) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", where); 
           return true;
        }
        catch(Exception ex) {
            LOGGER.warning(ex.toString());
            return false;
        }
    }
    public static DoctorOrder getDoctorOrderFromTreatmentRecordId(int tRecId) throws Exception {
       setupLog();
       DoctorOrder docOrd = null;
       MyQuery mq = new MyQuery();
       ResultSet rs = null;
       String[] where = {Integer.toString(tRecId)};
       try {
           rs = mq.executeQueryWhere("SELECT * from ron.docorder where trecid = ?", where);
       }catch(Exception ex) {
           
       }
       
       while(rs.next()) {
           docOrd = getDoctorOrderFromID(rs.getInt("recordid"));
           
       }
       
        return docOrd;
    }
    
    
    public static void deleteAppointnment(Appointment a) {
        setupLog();
        try {
        MyQuery mq = new MyQuery();
        String[] where = {Integer.toString(a.getDesiredDoctorID()), Integer.toString(a.getPatient().getUserID())
                , a.getAppDate(), a.getAppointmentTime(),a.getReasonForApp() };
        
        mq.executeUpdateWhere("DELETE FROM ron.appointment where docid = ? " +
        		"AND pid = ? AND appdate = ? " +
        		"AND apptime = ? AND reason = ?", where);
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
    }
    
    
    /**
     * Private method, will setup loggin on the server
     */
    private static void setupLog() {
        if(!logOn) {
            UseLogger logger = new UseLogger();
            try {
                MyLogger.setup();
                Date d = new Date();
                SimpleDateFormat sdf = new SimpleDateFormat("hh:mm");
                System.out.println("Created log at " + sdf.format(d));
            } catch (IOException e) {
                e.printStackTrace();
                throw new RuntimeException("Problems with creating the log files");
            }
            
            LOGGER.setLevel(Level.INFO);
            LOGGER.severe("Creating a severe info log");
            logOn = true;
        }

    }
    

}
